﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using Agile.Manager.Util;

namespace Agile.Manager.SQLite
{   
    public class SQLiteDataManagement
    {
        SQLite sql = new SQLite();
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();

        public SQLiteDataManagement()
        {
            CreateIfNotExist();
        }
        
        private void CreateIfNotExist()
        {
            DataTable table = sql.ExecuteQuery("select * from sqlite_master;");
            
            if (table.Rows.Count == 0) //DB doesn't exist
                CreateDataBase();
        }

        private void CreateDataBase()
        {
            //TODO: Improve with reflections the database creation, reading the entities in the Agile.Manager.Windows.Model library

            //add a Task table
            sql.ExecuteNonQuery(
              "CREATE TABLE Task" +
              "(ID TEXT PRIMARY KEY, Name TEXT, TaskID TEXT, Resources TEXT, Duration TEXT, Progress TEXT, Responsible TEXT)");
        }

        public List<T> GetData<T>()
        {
            CreateIfNotExist();
            List<T> rtnList = new List<T>();
            Type t = typeof(T);
            PropertyInfo[] pi = t.GetProperties();
            string TableName = t.Name;
            DT = sql.ExecuteQuery("Select * from " + TableName + ";");
            if (DT.Rows.Count == 0) return rtnList;

            foreach (DataRow itemRow in DT.Rows)
            {
                T row;
                row = Activator.CreateInstance<T>();

                foreach (DataColumn itemColumn in DT.Columns)
                {
                    foreach (PropertyInfo itemProperty in pi)
                    {
                        if (IsFieldType(itemProperty))
                        {
                            if (itemColumn.ColumnName == itemProperty.Name)
                                if (itemProperty.CanWrite)
                                    itemProperty.SetValue(row, itemRow[itemColumn.ColumnName], null);
                                else
                                    itemProperty.SetValue(row, null, null);
                        }
                    }
                }

                rtnList.Add(row);
            }

            return rtnList;
        }

        public bool Save<T>(T record)
        {
            try
            {
                if (!Insert<T>(record))
                    if (!Edit<T>(record))
                        return false;
            }
            catch (Exception)
            {
                throw;
            }

            return true;
        }

        private bool Insert<T>(T record)
        {
            string txtSQLQuery = GetInsertQuery<T>(record);

            try
            {
                if (sql.ExecuteNonQuery(txtSQLQuery) > 0)
                    return true;
            }
            catch (Exception)
            {
                //throw new Exception("Error Inserting: " + exc.Message); ;
            }

            return false;
        }

        private bool Edit<T>(T record)
        {
            string txtSQLQuery = GetEditQuery<T>(record);
            try
            {
                if (sql.ExecuteNonQuery(txtSQLQuery) > 0)
                    return true;
            }
            catch (Exception)
            {
                //throw new Exception("Error Editing: " + exc.Message); ;
            }

            return false;
        }

        public bool Delete<T>(T record)
        {
            string txtSQLQuery = GetDeleteQuery<T>(record);

            try
            {
                if (sql.ExecuteNonQuery(txtSQLQuery) > 0)
                    return true;
            }
            catch (Exception)
            {
                //throw new Exception("Error Deleting: " + exc.Message); ;
            }

            return false;
        }

        private string GetInsertQuery<T>(T record)
        {
            Type t = typeof(T);
            PropertyInfo[] pi = t.GetProperties();
            string TableName = t.Name;

            string valuescolumns = string.Empty;
            string values = string.Empty;
            foreach (PropertyInfo item in pi)
            {
                if (IsFieldType(item))
                {
                    if (string.IsNullOrEmpty(valuescolumns))
                        valuescolumns = "insert into " + TableName + " (";
                    else
                        valuescolumns = string.Concat(valuescolumns, ",");

                    if (string.IsNullOrEmpty(values))
                        values = " values (";
                    else
                        values = string.Concat(values, ",");


                    valuescolumns = string.Concat(valuescolumns, item.Name);

                    values = string.Concat(values, "'" + item.GetValue(record, null) + "'");
                }
            }

            valuescolumns = string.Concat(valuescolumns, ")");
            values = string.Concat(values, ");");

            return string.Concat(valuescolumns, values);
        }

        private string GetEditQuery<T>(T record)
        {
            Type t = typeof(T);
            PropertyInfo[] pi = t.GetProperties();
            string TableName = t.Name;

            string pk = GetPK(TableName);

            string sqlwhere = string.Empty;
            string valuescolumns = string.Empty;
            foreach (PropertyInfo item in pi)
            {
                if (IsFieldType(item))
                {
                    if (item.Name != pk)
                    {
                        if (string.IsNullOrEmpty(valuescolumns))
                            valuescolumns = "update " + TableName + " set ";
                        else
                            valuescolumns = string.Concat(valuescolumns, ",");

                        valuescolumns = string.Concat(valuescolumns, item.Name, " = '" + item.GetValue(record, null) + "'");
                    }
                    else
                    {
                        sqlwhere = " where " + pk + "='" + item.GetValue(record, null) + "';";
                    }
                }
            }

            return string.Concat(valuescolumns, sqlwhere);
        }

        private string GetDeleteQuery<T>(T record)
        {
            Type t = typeof(T);
            PropertyInfo[] pi = t.GetProperties();
            string TableName = t.Name;

            string pk = GetPK(TableName);
            string txtSQLQuery = "delete from  "+TableName;
            
            foreach (PropertyInfo item in pi)
            {
                if (IsFieldType(item))
                {
                    if (string.Equals(item.Name, pk))
                    {
                        txtSQLQuery = string.Concat(txtSQLQuery, " where " + pk + "='" + item.GetValue(record, null) + "';");
                    }
                }
            }

            return txtSQLQuery;
        }

        private string GetPK(string TableName)
        {
            string txtSqlQuery = "pragma table_info(" + TableName + ");";
            DT = sql.ExecuteQuery(txtSqlQuery);

            if (DT.Rows.Count != 0)
            {
                foreach (DataRow item in DT.Rows)
                {
                    if (item["pk"] != null)
                        if (item["pk"].ToString() == "1")
                            return (string)item["name"];
                }
            }

            return string.Empty;
        }

        private bool IsFieldType(PropertyInfo itemProperty)
        {
            foreach (Attribute attr in itemProperty.GetCustomAttributes(true))
            {
                AttributeElementType elementTypeAtrr = attr as AttributeElementType;

                if (elementTypeAtrr != null)
                {
                    if (elementTypeAtrr.ValidOn == ElementType.DataField)
                    {
                        return true;
                    }
                }
            }

            return false;
        }
    }
}